package com.softace.jdbc;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import com.softace.jdbc.annotations.JdbcAttribute;
import com.softace.jdbc.annotations.JdbcEntity;
import com.softace.jdbc.annotations.JdbcAttributeObject;
import com.softace.util.StackTracer;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * This is Core of the Database class which do the following tasks: <br> 1.
 * Establishes connection with the Database<br> 2. Perform Transactions between
 * the Database and the Layers above this class<br> 3. Disconnect from database
 * and release the resources<br>This class contains all methods needed to query
 * the database. It can be used to perform any DML (insert, update, delete )
 * operation.
 *
 * @author Sarfraz Ahmad
 * @author S1LENT W@RRIOR
 * @version 1.0
 */
public final class MySqlJdbcUtilities {

    private static IConnectionParameters jdbcParameters; // The IConnectionParameters object
    private Connection connection; // Connection object to establish the connectin to the database

    private static final Map<String, Method> typesMap = new HashMap(35);

    static {
        try {
            Class rsClass = ResultSet.class;
            typesMap.put("Object", rsClass.getMethod("getObject", String.class));
            typesMap.put("boolean", rsClass.getMethod("getBoolean", String.class));
            typesMap.put("Boolean", rsClass.getMethod("getBoolean", String.class));
            typesMap.put("byte", rsClass.getMethod("getByte", String.class));
            typesMap.put("Byte", rsClass.getMethod("getByte", String.class));
            typesMap.put("short", rsClass.getMethod("getShort", String.class));
            typesMap.put("Short", rsClass.getMethod("getShort", String.class));
            typesMap.put("int", rsClass.getMethod("getInt", String.class));
            typesMap.put("Integer", rsClass.getMethod("getInt", String.class));
            typesMap.put("long", rsClass.getMethod("getLong", String.class));
            typesMap.put("Long", rsClass.getMethod("getLong", String.class));
            typesMap.put("float", rsClass.getMethod("getFloat", String.class));
            typesMap.put("Float", rsClass.getMethod("getFloat", String.class));
            typesMap.put("double", rsClass.getMethod("getDouble", String.class));
            typesMap.put("Double", rsClass.getMethod("getDouble", String.class));
            typesMap.put("byte[]", rsClass.getMethod("getBytes", String.class));
            typesMap.put("Array", rsClass.getMethod("getArray", String.class));
            typesMap.put("URL", rsClass.getMethod("getURL", String.class));
            typesMap.put("Ref", rsClass.getMethod("getRef", String.class));
            typesMap.put("String", rsClass.getMethod("getString", String.class));
            typesMap.put("Time", rsClass.getMethod("getTime", String.class));
            typesMap.put("Date", rsClass.getMethod("getDate", String.class));
            typesMap.put("BigDecimal", rsClass.getMethod("getBigDecimal", String.class));
            typesMap.put("Timestamp", rsClass.getMethod("getTimestamp", String.class));
            typesMap.put("InputStream", rsClass.getMethod("getBinaryStream", String.class));
            typesMap.put("ByteArrayInputStream", rsClass.getMethod("getBinaryStream", String.class));
            typesMap.put("Reader", rsClass.getMethod("getCharacterStream", String.class));
            typesMap.put("InputStreamReader", rsClass.getMethod("getCharacterStream", String.class));
            typesMap.put("Blob", rsClass.getMethod("getBlob", String.class));
            typesMap.put("Clob", rsClass.getMethod("getClob", String.class));
            typesMap.put("NClob", rsClass.getMethod("getNClob", String.class));
            typesMap.put("SQLXML", rsClass.getMethod("getSQLXML", String.class));
        } catch (NoSuchMethodException ex) {
            StackTracer.printStackTrace(ex);
        } catch (SecurityException ex) {
            StackTracer.printStackTrace(ex);
        }
    }

    /**
     * The only constructor of this class. It Attempts to establish a connection
     * with the Database
     *
     * @param params the connection parameters
     */
    public MySqlJdbcUtilities(IConnectionParameters params) {
        jdbcParameters = params;
        this.openConnection();
    }

    /**
     * Gets the {@link java.sql.Connection}
     *
     * @return The connection object
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * Establishes the connection with the Database
     *
     */
    public void openConnection() {

        try {
            if (connection == null || connection.isClosed()) {
                String driverName = jdbcParameters.getDriverName();
                String connectionString = jdbcParameters.getConnectionString()
                        + jdbcParameters.getDatabaseName();
                String userName = jdbcParameters.getUsername();
                String password = jdbcParameters.getPassword();

                Class.forName(driverName); // Setting the driver

                // Establishing the connection with the database
                connection = DriverManager.getConnection(connectionString, userName, password);
            }
        } catch (ClassNotFoundException ex) {
            // In case of any SQLException
            StackTracer.printStackTrace(ex);
        } catch (SQLException ex) {
            // In case of any SQLException
            StackTracer.printStackTrace(ex);
        }
    }

    /**
     * Returns a PreparedStatement of the given connection, set with the given
     * SQL query and the given parameter values.<br>Note: This method must be
     * used for SELECT and UPDATE queries. To Prepare an INSERT query, use
     * {@link MySqlJdbcUtilities#prepareInsertStatement(String, Object...)}
     * method<br>Side Note: You can use this method for INSERT query as well but
     * you won't be able to get AUTO GENERATED keys in return!
     *
     * @param query The SQL query to construct the PreparedStatement with.
     * @param values The parameter values to be set in the created
     * PreparedStatement.
     * @return The Prepared Statement object
     * @throws SQLException If something fails while creating the
     * PreparedStatement.
     */
    public PreparedStatement prepareStatement(String query, Object... values) throws SQLException {
        this.openConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        this.setValues(preparedStatement, values);
        return preparedStatement;
    }

    /**
     * Returns a PreparedStatement of the given connection, set with the given
     * SQL query and the given parameter values.<br>Note: This method is
     * suggested to be used for INSERT queries only. To Prepare a SELECT or
     * UPDATE query, use
     * {@link MySqlJdbcUtilities#prepareStatement(String query, Object... values)}
     * method
     * <br>Side Note: You can use this method for SELECT or UPDATE queries as
     * well but you won't get an UPDATEABLE ResultSet
     *
     * @param query The SQL query to construct the PreparedStatement with.
     * @param values The parameter values to be set in the created
     * PreparedStatement.
     * @return The Prepared Statement object
     * @throws SQLException If something fails while creating the
     * PreparedStatement.
     */
    public PreparedStatement prepareInsertStatement(String query, Object... values) throws SQLException {
        this.openConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        this.setValues(preparedStatement, values);
        return preparedStatement;
    }

    /**
     * Set the given parameter values in the given
     * {@link java.sql.PreparedStatement}.
     *
     * @param preparedStatement the prepared statement
     * @param values The parameter values to be set in the created
     * PreparedStatement.
     * @throws SQLException If something fails while setting the
     * PreparedStatement values.
     */
    private void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
        if (values != null) {
            for (int i = 0; i < values.length; i++) {
                preparedStatement.setObject(i + 1, values[i]);
            }
        }
    }

    /**
     * Inserts row(s) into the database and returns its Auto Generated
     * Key<br>Note: You can INSERT rows using
     * {@code executeUpdate(String query, Object... values)} method. But this
     * this method is useful if you need the Auto-Generated Key in return
     *
     * @param query The SQL string having an INSERT query
     * @param values The input value(s)
     * @return The Auto Generated Key
     * @throws SQLException if INSERT fails
     */
    public Integer executeInsert(String query, Object... values) throws SQLException {
        this.openConnection();
        PreparedStatement preparedStatement = this.prepareInsertStatement(query, values);
        preparedStatement.executeUpdate();
        ResultSet rs = preparedStatement.getGeneratedKeys();
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    /**
     * Executes any DML (INSERT, UPDATE or DELETE) query on the Database and
     * return the number of rows affected.<br>May also return 0 for a query
     * returning no result.
     *
     * @param query The SQL string having an UPDATE query
     * @param values The input value(s)
     * @return either (1) the row count for SQL Data Manipulation Language (DML)
     * statements or (2) 0 for SQL statements that return nothing
     * @throws SQLException if UPDATE fails
     */
    public Integer executeUpdate(String query, Object... values) throws SQLException {
        this.openConnection();
        PreparedStatement preparedStatement = this.prepareStatement(query, values);
        return preparedStatement.executeUpdate();
    }

    /**
     * Retrieves Records from the database. Can take any number of input
     * parameters as search criterion
     *
     * @param query the SQL string having a SELECT query
     * @param values the input key(s) or the search criteria
     * @return the ResultSet of the given query
     * @throws SQLException if a database access error occurs; this method is
     * called on a closed PreparedStatement or the SQL statement does not return
     * a ResultSet object
     */
    public ResultSet selectFromDatabase(String query, Object... values) throws SQLException {
        this.openConnection();
        ResultSet resultSet;
        PreparedStatement preparedStatement = this.prepareStatement(query, values);
        resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    /**
     * Gets an object of type {@code T}, annotated with {@link JdbcEntity} from
     * the given query.
     *
     * @param <T> The generic type object to be returned
     * @param query The query to be executed
     * @param values The input key or the search criteria
     * @param clazz The class of the generic object to be returned
     * @return The objects {@code Map} of the given type {@code T} or
     * {@code null} if the {@code ResultSet} is either closed or the cursor is
     * after the last row.
     * @throws Exception in following cases:<br>
     * <pre>
     * 1. If {@code clazz} is null.
     * 2. If a database access error occurs.
     * 3. If this method is called on a closed PreparedStatement.
     * 4. If the SQL statement does not return a ResultSet object.
     * 5. All exceptions thrown by {@link java.lang.reflect.Method#invoke(Object, Object...)}
     * 6. All exceptions thrown by {@link java.lang.Class#newInstance()}
     * 7. All exceptions thrown by {@link java.lang.reflect.Field#set(Object, Object)}
     * </pre>
     */
    public <T> T getObjectFromQuery(String query, Class<T> clazz, Object... values) throws Exception {
        if (clazz == null) {
            throw new NullPointerException("Class can not be null!");
        }
        ResultSet rs = this.selectFromDatabase(query, values);
        return this.getObjectFromResultSet(rs, clazz);
    }

    /**
     * Gets a {@link java.util.List} of objects of type {@code T}, annotated
     * with {@link JdbcEntity} from the given query.
     *
     * @param <T> The generic type object to be returned
     * @param query The query to be executed
     * @param values The input key or the search criteria
     * @param clazz The class of the generic object to be returned
     * @return The objects {@code List} of the given type {@code T} or
     * {@code null} if the {@code ResultSet} is either closed or the cursor is
     * after the last row.
     * @throws Exception in following cases:<br>
     * <pre>
     * 1. If {@code clazz} is null.
     * 2. If a database access error occurs.
     * 3. If this method is called on a closed PreparedStatement.
     * 4. If the SQL statement does not return a ResultSet object.
     * 5. All exceptions thrown by {@link java.lang.reflect.Method#invoke(Object, Object...)}
     * 6. All exceptions thrown by {@link java.lang.Class#newInstance()}
     * 7. All exceptions thrown by {@link java.lang.reflect.Field#set(Object, Object)}
     * </pre>
     */
    public <T> List<T> getObjectsAsListFromQuery(String query, Class<T> clazz, Object... values) throws Exception {
        if (clazz == null) {
            throw new NullPointerException("Class can not be null!");
        }
        ResultSet rs = this.selectFromDatabase(query, values);
        return this.getObjectsAsListFromResultSet(rs, clazz);
    }

    /**
     * Gets a {@link java.util.Map} of objects of type {@code T}, annotated with
     * {@link JdbcEntity} from the given query.
     *
     * @param <K> The type of the key for the Map
     * @param <V> The generic type object to be returned
     * @param query The query to be executed
     * @param values The input key or the search criteria
     * @param clazz The class of the generic object to be returned
     * @return The objects {@code Map} of the given type {@code T} or
     * {@code null} if the {@code ResultSet} is either closed or the cursor is
     * after the last row.
     * @throws Exception in following cases:<br>
     * <pre>
     * 1. If {@code clazz} is null.
     * 2. If a database access error occurs.
     * 3. If this method is called on a closed PreparedStatement.
     * 4. If the SQL statement does not return a ResultSet object.
     * 5. All exceptions thrown by {@link java.lang.reflect.Method#invoke(Object, Object...)}
     * 6. All exceptions thrown by {@link java.lang.Class#newInstance()}
     * 7. All exceptions thrown by {@link java.lang.reflect.Field#set(Object, Object)}
     * </pre>
     */
    public <K extends Object, V> Map<K, V> getObjectsAsMapFromQuery(String query, Class<V> clazz,
            Object... values) throws Exception {
        if (clazz == null) {
            throw new NullPointerException("Class can not be null!");
        }
        ResultSet rs = this.selectFromDatabase(query, values);
        return this.getObjectsAsMapFromResultSet(rs, clazz);
    }

    /**
     * Gets an object of type {@code T}, annotated with {@link JdbcEntity} from
     * the given {@code ResultSet} at the specified row number. The row number
     * must not be zero negative.<br>Note: This method does not close the
     * {@code ResultSet}. The caller of this method is responsible for closing
     * the {@code ResultSet}.
     *
     * @param <T> The generic type object to be returned
     * @param resultSet The {@code ResultSet} object from which the generic
     * object is to be extracted
     * @param clazz The class of the generic object to be returned
     * @param row The row number starting from 1,2,3,....
     * @return The object of the given type {@code T} or {@code null} if the
     * {@code ResultSet} is either closed or the row number is zero or negative
     * @throws Exception in following cases:<br>
     * <pre>
     * 1. If {@code clazz} is null.
     * 2. If the given {@code ResultSet} is  null
     * 3. All exceptions thrown by {@link java.lang.reflect.Method#invoke(Object, Object...)}
     * 4. All exceptions thrown by {@link java.lang.Class#newInstance()}
     * 5. All exceptions thrown by {@link java.lang.reflect.Field#set(Object, Object)}
     * </pre>
     */
    public <T> T getObjectFromResultSet(ResultSet resultSet, Class<T> clazz, int row) throws Exception {
        if (clazz == null) {
            throw new NullPointerException("Class can not be null!");
        }
        int currentCounter = resultSet.getRow();
        if (row <= 0) {
            return null;
        } else if (row == 1) {
            resultSet.beforeFirst();
        } else {
            resultSet.absolute(row - 1);
        }
        T object = this.getObjectFromResultSet(resultSet, clazz);
        resultSet.absolute(currentCounter);
        return object;
    }

    /**
     * Gets an object of type {@code T}, annotated with {@link JdbcEntity} from
     * the given {@code ResultSet}.<br>Note: This method does not close the
     * {@code ResultSet}. The caller of this method is responsible for closing
     * the {@code ResultSet}.
     *
     * @param <T> The generic type object to be returned
     * @param resultSet The {@code ResultSet} object from which the generic
     * object is to be extracted
     * @param clazz The class of the generic object to be returned
     * @return The object of the given type {@code T} or {@code null} if the
     * {@code ResultSet} is either closed or the cursor is after the last row.
     * @throws Exception in following cases:<br>
     * <pre>
     * 1. If {@code clazz} is null.
     * 2. If the given {@code ResultSet} is  {@code null}
     * 3. All exceptions thrown by {@link java.lang.reflect.Method#invoke(Object, Object...)}
     * 4. All exceptions thrown by {@link java.lang.Class#newInstance()}
     * 5. All exceptions thrown by {@link java.lang.reflect.Field#set(Object, Object)}
     * </pre>
     */
    public <T> T getObjectFromResultSet(ResultSet resultSet, Class<T> clazz) throws Exception {
        if (clazz == null) {
            throw new NullPointerException("Class can not be null!");
        }

        if (resultSet.isAfterLast() || resultSet.isClosed()) {
            return null;
        }

        JdbcResultSetMetaData metaData = JdbcManager.getMetaDataForResultSet(resultSet);
        if (resultSet.getRow() >= metaData.getRowCount()) {
            return null;
        }

        Map<String, Field> fieldsMap = this.getFieldsMapForClass(clazz, metaData);
        Map<String, JdbcFieldData> row = new LinkedHashMap();
        if (resultSet.next()) {
            for (Map.Entry<String, JdbcColumn> entry : metaData.getColumns().entrySet()) {
                String columnTitle = entry.getKey();
                Field field = fieldsMap.get(columnTitle);
                if (field != null) {
                    Method method = typesMap.get(field.getType().getSimpleName());
                    JdbcFieldData fieldData = null;
                    try {
                        Object columnValue = method.invoke(resultSet, columnTitle);
                        fieldData = new JdbcFieldData(field, columnValue, entry.getValue());
                    } catch (Exception ex) {
                        if (ex.getCause() instanceof SQLException) {

                        } else {
                            throw ex;
                        }
                    }
                    row.put(columnTitle, fieldData);
                }
            }
        }
        return this.getNewObject(row, metaData, clazz, null);
    }

    /**
     * Gets a {@link java.util.List} of objects of type {@code T}, annotated
     * with {@link JdbcEntity} from the given {@code ResultSet}.<br>Note: This
     * method does not close the {@code ResultSet}. The caller of this method is
     * responsible for closing the {@code ResultSet}.
     *
     * @param <T> The generic type object to be returned
     * @param resultSet The {@code ResultSet} object from which the generic
     * object is to be extracted
     * @param clazz The class of the generic object to be returned
     * @return The objects {@code List} of the given type {@code T} or
     * {@code null} if the {@code ResultSet} is either closed or the cursor is
     * after the last row.
     * @throws Exception in following cases:<br>
     * <pre>
     * 1. If {@code clazz} is null.
     * 2. If the given {@code ResultSet} is  null
     * 3. All exceptions thrown by {@link java.lang.reflect.Method#invoke(Object, Object...)}
     * 4. All exceptions thrown by {@link java.lang.Class#newInstance()}
     * 5. All exceptions thrown by {@link java.lang.reflect.Field#set(Object, Object)}
     * </pre>
     */
    public <T> List<T> getObjectsAsListFromResultSet(ResultSet resultSet, Class<T> clazz) throws Exception {
        if (clazz == null) {
            throw new NullPointerException("Class can not be null!");
        }
        JdbcResultSetMetaData metaData = JdbcManager.getMetaDataForResultSet(resultSet);
        List<Map<String, JdbcFieldData>> rows = this.getRowsMapFromResultSet(resultSet, metaData, clazz);

        List<T> objects = new ArrayList();
        for (Map<String, JdbcFieldData> row : rows) {
            objects.add(this.getNewObject(row, metaData, clazz, null));
        }
        return objects;
    }

    /**
     * Gets a {@link java.util.Map} of objects of type {@code T}, annotated with
     * {@link JdbcEntity} from the given {@code ResultSet}.<br>Note: This method
     * does not close the {@code ResultSet}. The caller of this method is
     * responsible for closing the {@code ResultSet}.
     *
     * @param <K> The type of the key for the Map
     * @param <V> The generic type object to be returned
     * @param resultSet The {@code ResultSet} object from which the generic
     * object is to be extracted
     * @param clazz The class of the generic object to be returned
     * @return The objects {@code Map} of the given type {@code T} or
     * {@code null} if the {@code ResultSet} is either closed or the cursor is
     * after the last row.
     * @throws Exception in following cases:<br>
     * <pre>
     * 1. If {@code clazz} is null.
     * 2. If the given {@code ResultSet} is  null
     * 3. All exceptions thrown by {@link java.lang.reflect.Method#invoke(Object, Object...)}
     * 4. All exceptions thrown by {@link java.lang.Class#newInstance()}
     * 5. All exceptions thrown by {@link java.lang.reflect.Field#set(Object, Object)}
     * </pre>
     */
    public <K extends Object, V> Map<K, V> getObjectsAsMapFromResultSet(ResultSet resultSet, Class<V> clazz)
            throws Exception {
        if (clazz == null) {
            throw new NullPointerException("Class can not be null!");
        }

        JdbcResultSetMetaData metaData = JdbcManager.getMetaDataForResultSet(resultSet);
        List<Map<String, JdbcFieldData>> rows = this.getRowsMapFromResultSet(resultSet, metaData, clazz);
        Field column = this.getPrimaryKeyColumn(clazz);
        if (column == null) {
            throw new Exception("No Primary key column defined!");
        }

        Map objects = new LinkedHashMap();
        for (Map<String, JdbcFieldData> row : rows) {
            V object = this.getNewObject(row, metaData, clazz, null);
            objects.put(column.get(object), object);
        }
        return objects;
    }

    private <T> Field getPrimaryKeyColumn(Class<T> clazz) {
        JdbcEntity entity = (JdbcEntity) clazz.getAnnotation(JdbcEntity.class);
        Class parentClass = entity.parentEntityClass();
        if (parentClass.equals(JdbcEntity.class)) {
            for (Field field : clazz.getDeclaredFields()) {
                JdbcAttribute annotation = field.getAnnotation(JdbcAttribute.class);
                if (annotation != null) {
                    if (annotation.isKeyForMap()) {
                        field.setAccessible(true);
                        return field;
                    }
                }
            }
            return null;
        } else {
            return this.getPrimaryKeyColumn(parentClass);
        }
    }

    private List<Map<String, JdbcFieldData>> getRowsMapFromResultSet(ResultSet rs,
            JdbcResultSetMetaData metaData, Class clazz) throws Exception {
        List<Map<String, JdbcFieldData>> rows = new ArrayList();
        Map<String, Field> fieldsMap = this.getFieldsMapForClass(clazz, metaData);
        while (rs.next()) {
            Map<String, JdbcFieldData> innerObjects = new LinkedHashMap();
            for (Map.Entry<String, JdbcColumn> entry : metaData.getColumns().entrySet()) {
                String columnTitle = entry.getKey();
                Field field = fieldsMap.get(columnTitle);
                if (field != null) {
                    field.setAccessible(true);
                    Method method = typesMap.get(field.getType().getSimpleName());
                    JdbcFieldData fieldData = null;
                    try {
                        Object columnValue = method.invoke(rs, columnTitle);
                        fieldData = new JdbcFieldData(field, columnValue, entry.getValue());
                    } catch (Exception ex) {
                        if (ex.getCause() instanceof SQLException) {

                        } else {
                            System.err.println("Error: " + ex.getMessage());
                            throw ex;
                        }
                    }
                    innerObjects.put(columnTitle, fieldData);
                }
            }
            rows.add(innerObjects);
        }
        return rows;
    }

    @SuppressWarnings(value = "convert recursion to iteration")
    private Map<String, Field> getFieldsMapForClass(Class clazz, JdbcResultSetMetaData metaData) {
        Map<String, Field> fieldsMap = new LinkedHashMap();
        JdbcEntity entity = (JdbcEntity) clazz.getAnnotation(JdbcEntity.class);
        Class parentClass = entity.parentEntityClass();
        if (!parentClass.equals(JdbcEntity.class)) {
            Map<String, Field> fields = this.getFieldsMapForClass(parentClass, metaData);
            fieldsMap.putAll(fields);
        }
        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            JdbcAttributeObject entityObject = (JdbcAttributeObject) field.getAnnotation(JdbcAttributeObject.class);
            if (entityObject == null) { // plain object
                JdbcAttribute attribute = field.getAnnotation(JdbcAttribute.class);
                if (attribute != null) {
                    String fieldTitle = entity.tableName() + "." + attribute.columnName();
                    if (!attribute.tableName().isEmpty()) {
                        fieldTitle = attribute.tableName() + "." + attribute.columnName();
                    }
                    JdbcColumn column = metaData.findColumnFromTitle(fieldTitle);
                    if (column != null) {
                        fieldsMap.put(column.getColumnTitle(), field);
                    }
                }
            } else {
                Map<String, Field> fields = this.getFieldsMapForClass(entityObject.tableClass(),
                        metaData);
                fieldsMap.putAll(fields);
            }
        }
        return fieldsMap;
    }

    private <T> T getNewObject(Map<String, JdbcFieldData> row, JdbcResultSetMetaData metaData,
            Class<T> clazz, T newInstance) throws Exception {
        JdbcEntity entity = (JdbcEntity) clazz.getAnnotation(JdbcEntity.class);
        if (newInstance == null) {
            newInstance = clazz.newInstance();
        }

        Class superClass = entity.parentEntityClass();
        if (superClass != null && superClass.getAnnotation(JdbcEntity.class) != null) {
            this.getNewObject(row, metaData, superClass, newInstance);
        }

        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            JdbcAttributeObject entityObject = field.getAnnotation(JdbcAttributeObject.class);
            if (entityObject == null) { // plain object
                JdbcAttribute attribute = field.getAnnotation(JdbcAttribute.class);
                if (attribute != null) {
                    String title = entity.tableName() + "." + attribute.columnName();
                    if (!attribute.tableName().isEmpty()) {
                        title = attribute.tableName() + "." + attribute.columnName();
                    }
                    JdbcColumn column = metaData.findColumnFromTitle(title);
                    if (column != null) {
                        JdbcFieldData fieldData = row.get(column.getColumnTitle());
                        Object fieldValue = fieldData.getFieldValue();
                        field.set(newInstance, fieldValue);
                    }
                }
            } else {
                if (field.getType().equals(entityObject.tableClass())) {
                    boolean hasTable = false;
                    for (String table : metaData.getTables()) {
                        if (table.equalsIgnoreCase(entityObject.tableName())) {
                            hasTable = true;
                            break;
                        }
                    }
                    if (hasTable) {
                        Object fieldValue = this.getNewObject(row, metaData,
                                entityObject.tableClass(), null);
                        field.set(newInstance, fieldValue);
                    }
                } else if (Iterable.class.isAssignableFrom(field.getType())) {

                } else if (Map.class.isAssignableFrom(field.getType())) {

                } else {

                }
            }
        }
        return newInstance;
    }

    /**
     * This method retrieves an integer value from the Database
     *
     * @param query the SQL string having a SELECT query
     * @param values the input key or the Search criteria
     * @return A single integer value
     * @throws SQLException if a database access error occurs; this method is
     * called on a closed PreparedStatement or the SQL statement does not return
     * a ResultSet object
     */
    public Integer getIntFromDatabase(String query, Object... values) throws SQLException {
        this.openConnection();
        PreparedStatement preparedStatement = this.prepareStatement(query, values);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.first();
        return resultSet.getInt(1);
    }

    /**
     * This method is same as {@link #getIntFromDatabase(String, Object...)}.
     * The only difference is that it returns a List of keys or integer values
     *
     * @param query the SQL string having a SELECT query
     * @param values the input key or the search criteria
     * @return List of integers
     * @throws SQLException if a database access error occurs; this method is
     * called on a closed PreparedStatement or the SQL statement does not return
     * a ResultSet object
     */
    public List<Integer> getIntsFromDatabase(String query, Object... values) throws SQLException {
        this.openConnection();
        List<Integer> idsFromDatabase = new ArrayList();
        PreparedStatement preparedStatement = this.prepareStatement(query, values);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            idsFromDatabase.add(resultSet.getInt(1));
        }
        return idsFromDatabase;
    }

    /**
     * This method deletes the data from the Database
     *
     * @param tableName The columnName of the table from which the data is to be
     * deleted
     * @param key The columnName of the key column, on which the row(s) are to
     * be deleted
     * @param id The value of the key column of the given table
     * @throws SQLException if a database access error occurs, this method is
     * called on a closed Statement, the given SQL statement produces a
     * ResultSet object, the method is called on a PreparedStatement or
     * CallableStatement
     */
    public void deleteFromDatabase(String tableName, String key, Object id) throws SQLException {
        this.openConnection();
        String query = String.format("DELETE FROM %s WHERE %s = '%s';", tableName, key, id);
        this.executeUpdate(query); // Executing the Query
    }

    /**
     * Truncates a given table
     *
     * @param tableName name of the table to be truncated
     * @throws SQLException if a database access error occurs, this method is
     * called on a closed Statement, the given SQL statement produces a
     * ResultSet object, the method is called on a PreparedStatement or
     * CallableStatement
     */
    public void truncate(String tableName) throws SQLException {
        this.openConnection();
        String query = String.format("TRUNCATE %s;", tableName);
        this.executeUpdate(query); // Executing the Query
    }

    /**
     * This method attempts to disconnect from the Database
     *
     * @param resultSet the resultSet object
     * @param statement the statement object
     */
    public final void disconnect(ResultSet resultSet, Statement statement) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException ex) {
                StackTracer.printStackTrace(ex);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                StackTracer.printStackTrace(ex);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
                StackTracer.printStackTrace(ex);
            }
        }
    }
}
